﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;
using SIPO.Library.DomainObject.Administration.Catalog;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recDemandRepo : ISearchable<recDemand>
    {
        
        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recDemand", column);
                            sbQuery.AppendFormat(@"         inner join transactions.recDemandDetail on recDemand.DemandNo = recDemandDetail.DemandNo 
                                                            {0}", parameters);

                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.recDemand", column);
                            sbQuery.AppendFormat(@"         inner join transactions.recDemandDetail on recDemand.DemandNo = recDemandDetail.DemandNo 
                                                            inner join master.recDebtor on recDebtor.DebtorID = recDemand.DebtorID  
                                                            inner join master.recTransactionType on recTransactionType.TransTypeID = recDemandDetail.TransTypeID
                                                            {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM transactions.recDemand", column);
                            sbQuery.AppendFormat(@"         inner join transactions.recDemandDetail on recDemand.DemandNo = recDemandDetail.DemandNo 
                                                            inner join master.recDebtor on recDebtor.DebtorID = recDemand.DebtorID  
                                                            inner join master.recTransactionType on recTransactionType.TransTypeID = recDemandDetail.TransTypeID
                                                            {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM transactions.recDemand", column);
                            sbQuery.AppendFormat(@"         inner join transactions.recDemandDetail on recDemand.DemandNo = recDemandDetail.DemandNo
                                                            inner join master.recDebtor on recDebtor.DebtorID = recDemand.DebtorID  
                                                            inner join master.recTransactionType on recTransactionType.TransTypeID = recDemandDetail.TransTypeID 
                                                            {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recDemand", column);
                            sbQuery.AppendFormat(@"         inner join transactions.recDemandDetail on recDemand.DemandNo = recDemandDetail.DemandNo 
                                                            inner join master.recDebtor on recDebtor.DebtorID = recDemand.DebtorID  
                                                            inner join master.recTransactionType on recTransactionType.TransTypeID = recDemandDetail.TransTypeID
                                                            {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recCorporateAction", column);
                            sbQuery.AppendFormat(@"         inner join transactions.recDemandDetail on recDemand.DemandNo = recDemandDetail.DemandNo 
                                                            inner join master.recDebtor on recDebtor.DebtorID = recDemand.DebtorID  
                                                            inner join master.recTransactionType on recTransactionType.TransTypeID = recDemandDetail.TransTypeID
                                                            {0}", parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public object SelectScalarMonitoring(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat(@"         inner join master.recDebtorSector on recBilling.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                            inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                            inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID  {0}", parameters);

                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat(@"         inner join master.recDebtorSector on recBilling.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                            inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                            inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID  {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat(@"         inner join master.recDebtorSector on recBilling.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                            inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                            inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID  {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat(@"         inner join master.recDebtorSector on recBilling.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                            inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                            inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID  {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat(@"         inner join master.recDebtorSector on recBilling.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                            inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                            inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID  {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat(@"         inner join master.recDebtorSector on recBilling.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                            inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                            inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID  {0}", parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public object SelectScalarMonitoringPenagihan(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat(@"         WITH result_set AS
                                                        (
                                                            select
                                                            ROW_NUMBER() OVER (ORDER BY recDemand.DebtorID) AS [row_number],
                                                            COUNT({0}) jumlah,
                                                            recDemand.DebtorID,
                                                            recDebtor.DebtorCode
                                                            ,recDemand.DebtorName
                                                            from
                                                            transactions.recDemand
                                                            inner join transactions.recDemandDetail
                                                            on recDemand.DemandNo = recDemandDetail.DemandNo
                                                            inner join master.recDebtor
                                                            on recDemand.DebtorID = recDebtor.DebtorID
                                                            {1}
                                                            group by recDemand.DebtorID, recDebtor.DebtorCode, recDemand.DebtorName
                                                        )
                                                        select MAX(*) from result_set ", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat(@"         WITH result_set AS
                                                        (
                                                            select
                                                            ROW_NUMBER() OVER (ORDER BY recDemand.DebtorID) AS [row_number],
                                                            COUNT({0}) jumlah,
                                                            recDemand.DebtorID,
                                                            recDebtor.DebtorCode,
                                                            recDemand.DebtorName,
                                                            recDemandDetail.TransName,
                                                            recDemandDetail.DueDate
                                                            from
                                                            transactions.recDemand
                                                            inner join transactions.recDemandDetail
                                                            on recDemand.DemandNo = recDemandDetail.DemandNo
                                                            inner join master.recDebtor
                                                            on recDemand.DebtorID = recDebtor.DebtorID
                                                            {1}
                                                            group by recDemand.DebtorID, 
                                                            recDebtor.DebtorCode, 
                                                            recDemand.DebtorName,
                                                            recDemandDetail.TransName,
                                                            recDemandDetail.DueDate
                                                        )
                                                        select MIN(*) from result_set ", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat(@"         WITH result_set AS
                                                        (
                                                            select
                                                            ROW_NUMBER() OVER (ORDER BY recDemand.DebtorID) AS [row_number],
                                                            COUNT({0}) jumlah,
                                                            recDemand.DebtorID,
                                                            recDebtor.DebtorCode,
                                                            recDemand.DebtorName,
                                                            recDemandDetail.TransName,
                                                            recDemandDetail.DueDate
                                                            from
                                                            transactions.recDemand
                                                            inner join transactions.recDemandDetail
                                                            on recDemand.DemandNo = recDemandDetail.DemandNo
                                                            inner join master.recDebtor
                                                            on recDemand.DebtorID = recDebtor.DebtorID
                                                            {1}
                                                            group by recDemand.DebtorID, 
                                                            recDebtor.DebtorCode, 
                                                            recDemand.DebtorName
                                                            recDemandDetail.TransName,
                                                            recDemandDetail.DueDate
                                                        )
                                                        select COUNT(*) from result_set ", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat(@"         WITH result_set AS
                                                        (
                                                            select
                                                            ROW_NUMBER() OVER (ORDER BY recDemand.DebtorID) AS [row_number],
                                                            COUNT({0}) jumlah,
                                                            recDemand.DebtorID,
                                                            recDebtor.DebtorCode
                                                            ,recDemand.DebtorName
                                                            ,recDemandDetail.TransName
                                                            ,recDemandDetail.DueDate
                                                            from
                                                            transactions.recDemand
                                                            inner join transactions.recDemandDetail
                                                            on recDemand.DemandNo = recDemandDetail.DemandNo
                                                            inner join master.recDebtor
                                                            on recDemand.DebtorID = recDebtor.DebtorID
                                                            {1}
                                                            group by recDemand.DebtorID, 
                                                            recDebtor.DebtorCode, 
                                                            recDemand.DebtorName,
                                                            recDemandDetail.TransName,
                                                            recDemandDetail.DueDate
                                                        )
                                                        select COUNT(*) from result_set ", column,parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat(@"         WITH result_set AS
                                                        (
                                                            select
                                                            ROW_NUMBER() OVER (ORDER BY recDemand.DebtorID) AS [row_number],
                                                            COUNT({0}) jumlah,
                                                            recDemand.DebtorID,
                                                            recDebtor.DebtorCode,
                                                            recDemand.DebtorName,
                                                            recDemandDetail.TransName,
                                                            recDemandDetail.DueDate
                                                            from
                                                            transactions.recDemand
                                                            inner join transactions.recDemandDetail
                                                            on recDemand.DemandNo = recDemandDetail.DemandNo
                                                            inner join master.recDebtor
                                                            on recDemand.DebtorID = recDebtor.DebtorID
                                                            {1}
                                                            group by 
                                                            recDemand.DebtorID, 
                                                            recDebtor.DebtorCode, 
                                                            recDemand.DebtorName,
                                                            recDemandDetail.TransName,
                                                            recDemandDetail.DueDate
                                                        )
                                                        select SUM(*) from result_set ", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat(@"         WITH result_set AS
                                                        (
                                                            select
                                                            ROW_NUMBER() OVER (ORDER BY recDemand.DebtorID) AS [row_number],
                                                            COUNT({0}) jumlah,
                                                            recDemand.DebtorID,
                                                            recDebtor.DebtorCode,
                                                            recDemand.DebtorName,
                                                            recDemandDetail.TransName,
                                                            recDemandDetail.DueDate
                                                            from
                                                            transactions.recDemand
                                                            inner join transactions.recDemandDetail
                                                            on recDemand.DemandNo = recDemandDetail.DemandNo
                                                            inner join master.recDebtor
                                                            on recDemand.DebtorID = recDebtor.DebtorID
                                                            {1}
                                                            group by recDemand.DebtorID, 
                                                            recDebtor.DebtorCode, 
                                                            recDemand.DebtorName,
                                                            recDemandDetail.TransName,
                                                            recDemandDetail.DueDate
                                                        )
                                                        select AVG(*) from result_set", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public bool insertRecDemand(string userLogin)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "exec [dbo].[SP_insertUpayaPenagihan] @userLogin";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@userLogin", SqlDbType.VarChar).Value = userLogin;

                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        result = true;
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }
            return result;
        }


        public int GetPelimpahan(int demandNo)
        {
            int usiaTagihan = -1;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat("declare @usiaTagihan int ");
                    sbQuery.AppendFormat("set @usiaTagihan = dbo.FN_usiaTagihan({0})", demandNo);
                    sbQuery.AppendFormat("select @usiaTagihan as usia_tagihan");

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {              
                                usiaTagihan = dr[0] == DBNull.Value ? 0 : int.Parse(dr[0].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return usiaTagihan;
        }

        public List<recBilling> GetListDebiturJatuhTempo(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recBilling> tt = new List<recBilling>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"      recDebtor.DebtorName
                                                ,recTransactionType.TransTypeName
                                                ,recBilling.Value
                                                ,recBilling.DueDate");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"      transactions.recBilling
                                                inner join master.recDebtorSector on recBilling.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                    sbQuery.AppendFormat("{0} ) ", parameters);
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recBilling t = new recBilling(true);
                                t.recDebtor.DebtorID = dr[0] == DBNull.Value ? 0 : int.Parse(dr[0].ToString()); 
                                t.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.recTransactionType.TransTypeName = dr[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recTransactionType.Fields.TransTypeName].ToString();
                                t.Value = dr[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr[recBilling.Fields.Value].ToString());
                                t.DueDate = dr[recBilling.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.DueDate].ToString());

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recDemand> GetListMonitoringUpayaPenagihan(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recDemand> tt = new List<recDemand>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"      COUNT(recDemand.DebtorID) jumlah,
                                                    recDemand.DemandNo,
                                                    recDemand.DebtorID,
                                                    recDebtor.DebtorCode
                                                    ,recDemand.DebtorName
                                                    ,recDemandDetail.TransName
                                                    ,recDemandDetail.DueDate");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"      transactions.recDemand
                                                inner join transactions.recDemandDetail
                                                on recDemand.DemandNo = recDemandDetail.DemandNo
                                                inner join master.recDebtor
                                                on recDemand.DebtorID = recDebtor.DebtorID
                                                {0}
                                                group by 
                                                recDemand.DemandNo,
                                                recDemand.DebtorID, 
                                                recDebtor.DebtorCode, 
                                                recDemand.DebtorName,
                                                recDemandDetail.TransName,
                                                recDemandDetail.DueDate", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDemand t = new recDemand(true);
                                t.DebtorID = dr[0] == DBNull.Value ? 0 : int.Parse(dr[0].ToString());
                                t.id = dr[recDemand.Fields.DemandNo] == DBNull.Value ? 0 : int.Parse(dr[recDemand.Fields.DemandNo].ToString());
                                t.recDebtor.DebtorName = dr[recDemand.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDemand.Fields.DebtorName].ToString();
                                t.recDebtor.DebtorCode = dr[recDebtor.Fields.DebtorCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorCode].ToString();
                                t.recDemandDetail.TransName = dr[recDemandDetail.Fields.TransName] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.TransName].ToString();
                                t.recDemandDetail.DueDate = dr[recDemandDetail.Fields.DueDate] == DBNull.Value ? (DateTime)(DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.DueDate].ToString());

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recDemand> GetListDetailMonitoringUpayaPenagihan(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recDemand> tt = new List<recDemand>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"  recDemandDetail.TransNo
                                            ,recDemandDetail.TransName
                                            ,recDemandDetail.TransValue
                                            ,recDemandDetail.[Status]
                                            ,recDemandDetail.LatterDate
                                            ,recDemandDetail.LatterNo
                                            ,recDemandDetail.DueDate
                                            ,recDemandDetail.DueDateLetter");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"      transactions.recDemand
                                                inner join transactions.recDemandDetail
                                                on recDemand.DemandNo = recDemandDetail.DemandNo");
                    sbQuery.AppendFormat("{0} ) ", parameters);
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDemand t = new recDemand(true);
                                t.DebtorID = dr[0] == DBNull.Value ? 0 : int.Parse(dr[0].ToString());
                                t.recDemandDetail.TransNo = dr[recDemandDetail.Fields.TransNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.TransNo].ToString();
                                t.recDemandDetail.TransName = dr[recDemandDetail.Fields.TransName] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.TransName].ToString();
                                t.recDemandDetail.TransValue = dr[recDemandDetail.Fields.TransValue] == DBNull.Value ? (decimal?)0 : decimal.Parse(dr[recDemandDetail.Fields.TransValue].ToString());
                                t.recDemandDetail.Status = dr[recDemandDetail.Fields.Status] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.Status].ToString();
                                t.recDemandDetail.LatterDate = dr[recDemandDetail.Fields.LatterDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.LatterDate].ToString());
                                t.recDemandDetail.LatterNo = dr[recDemandDetail.Fields.LatterNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.LatterNo].ToString();
                                t.recDemandDetail.DueDate = dr[recDemandDetail.Fields.DueDate] == DBNull.Value ? (DateTime)(DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.DueDate].ToString());
                                t.recDemandDetail.DueDateLetter = dr[recDemandDetail.Fields.DueDateLetter] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.DueDateLetter].ToString());

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recDemand> GetListDebtorOnRecDemand()
        {
            List<recDemand> tt = new List<recDemand>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"  distinct recDebtor.DebtorID
                                            ,recDebtor.DebtorCode
                                            ,recDebtor.DebtorName");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"      transactions.recDemand
                                                 inner join master.recDebtor on recDebtor.DebtorID = recDemand.DebtorID
                                                 where recDemand.LastAction is not null");
                    

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDemand t = new recDemand(true);
                                t.DebtorID = dr[recDemand.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDemand.Fields.DebtorID].ToString());
                                t.DebtorName = dr[recDemand.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDemand.Fields.DebtorName].ToString();
                                t.recDebtor.DebtorCode = dr[recDebtor.Fields.DebtorCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorCode].ToString();

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public recDemand GetListByDemandNo(int demandNo)
        {
            recDemand t = new recDemand(true);
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"  recDemand.DebtorName
                                            ,recDebtor.Email
                                            ,recDemandDetail.TransTypeID
                                            ,recDemandDetail.TransName
                                            ,recDemandDetail.TransNo
                                            ,recDemandDetail.DueDate
                                            ,recDemandDetail.DueDateLetter
                                            ,recDemandDetail.LatterDate
                                            ,recDemandDetail.LatterNo
                                            ,recDemandDetail.StatusID");
                    sbQuery.AppendFormat("    FROM ");

                    sbQuery.AppendFormat(@"      transactions.recDemand
                                                inner join transactions.recDemandDetail on recDemand.DemandNo = recDemandDetail.DemandNo
                                                inner join master.recDebtor on recDebtor.DebtorID = recDemand.DebtorID");
                    sbQuery.AppendFormat("  WHERE  ");
                    sbQuery.AppendFormat("      recDemand.DemandNo = {0}", demandNo);
                    

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t.DebtorName = dr[recDemand.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDemand.Fields.DebtorName].ToString();
                                t.recDebtor.Email = dr[recDebtor.Fields.Email] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Email].ToString();
                                t.recDemandDetail.TransTypeID = dr[recDemandDetail.Fields.TransTypeID] == DBNull.Value ? 0 : int.Parse(dr[recDemandDetail.Fields.TransTypeID].ToString());
                                t.recDemandDetail.TransName = dr[recDemandDetail.Fields.TransName] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.TransName].ToString();
                                t.recDemandDetail.TransNo = dr[recDemandDetail.Fields.TransNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.TransNo].ToString();
                                t.recDemandDetail.DueDate = dr[recDemandDetail.Fields.DueDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recDemandDetail.Fields.DueDate].ToString());
                                t.recDemandDetail.DueDateLetter = dr[recDemandDetail.Fields.DueDateLetter] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.DueDateLetter].ToString());
                                t.recDemandDetail.LatterDate = dr[recDemandDetail.Fields.LatterDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.LatterDate].ToString());
                                t.recDemandDetail.LatterNo = dr[recDemandDetail.Fields.LatterNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.LatterNo].ToString();
                                t.recDemandDetail.StatusID = dr[recDemandDetail.Fields.StatusID] == DBNull.Value ? 0 : int.Parse(dr[recDemandDetail.Fields.StatusID].ToString());
                             
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public DateTime? GetDueDateLetter(string latterDate, int termDay)
        {
            DateTime? t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat("select dbo.fn_getDueDateLetter('{0}',{1})", latterDate, termDay);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = dr[0] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[0].ToString());
                                
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public List<recActionDemand> GetListActionDemandByActionId(int key)
        {
            List<recActionDemand> tt = new List<recActionDemand>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    
                        sbQuery.AppendFormat("    SELECT ");
                        sbQuery.AppendFormat("  ActionID,ActionName");
                        sbQuery.AppendFormat("    FROM ");
                        sbQuery.AppendFormat("      master.recActionDemand");
                        sbQuery.AppendFormat("  WHERE  ");
                        sbQuery.AppendFormat("  ActionID not in (select recDemandDetail.StatusID");
                        sbQuery.AppendFormat("  from transactions.recDemandDetail");
                        sbQuery.AppendFormat("  inner join transactions.recDemand on recDemand.DemandNo = recDemandDetail.DemandNo");
                        sbQuery.AppendFormat("  where recDemand.DemandNo =  {0})", key);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recActionDemand t = new recActionDemand();
                                t.ActionID = dr[recActionDemand.Fields.ActionID] == DBNull.Value ? 0 : int.Parse(dr[recActionDemand.Fields.ActionID].ToString());
                                t.ActionName = dr[recActionDemand.Fields.ActionName] == DBNull.Value ? string.Empty : dr[recActionDemand.Fields.ActionName].ToString();

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public string GetActionDemandById(int ActionId)
        {
            string actionName = string.Empty;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder(); 
                        sbQuery.AppendFormat("    SELECT ");
                        sbQuery.AppendFormat("  ActionName");
                        sbQuery.AppendFormat("    FROM ");
                        sbQuery.AppendFormat("      master.recActionDemand");
                        sbQuery.AppendFormat("  WHERE  ");
                        sbQuery.AppendFormat("      ActionID = {0}", ActionId);
                   

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                actionName = dr[recActionDemand.Fields.ActionName] == DBNull.Value ? string.Empty : dr[recActionDemand.Fields.ActionName].ToString();
                                  
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return actionName;
        }

        public List<recDemand> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recDemand> tt = new List<recDemand>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY recDemand.DemandNo,{0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"      recDemandDetail.DemandDetailNo
                                                ,recDebtor.Email
                                            ,recDebtor.DebtorID
                                            ,recDemandDetail.DemandNo
                                            , recDemand.DebtorName
                                            ,recDemandDetail.TransName
                                            ,recDemandDetail.TransValue
                                            ,recDemandDetail.DueDate
                                            ,recDemandDetail.DueDateLetter
                                            ,recDemandDetail.LatterDate
                                            ,recDemandDetail.LatterNo
                                            ,recDemand.LastAction");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"      transactions.recDemand 
                                                inner join transactions.recDemandDetail on recDemand.DemandNo = recDemandDetail.DemandNo
                                                 inner join master.recDebtor on recDebtor.DebtorID = recDemand.DebtorID
                                                inner join master.recTransactionType on recTransactionType.TransTypeID = recDemandDetail.TransTypeID");
//                    sbQuery.AppendFormat("  WHERE  ");
//                    sbQuery.AppendFormat(@" recDemandDetail.DemandDetailNo in (select * from dbo.fn_maxListUpayaPenagihan()) and
//                                            recTransactionType.TransTypeID not in(1,4,6,7,9,10,11,12,16,17,22,23,27)  and
//                                            recDemand.IsActive = 1 {0}
//                                                and (convert(date,GETDATE()) <= recDemandDetail.DueDateLetter
//                                                or recDemandDetail.LatterNo is null    
//                                                or recDemandDetail.DueDateLetter is null", parameters);
                    sbQuery.AppendFormat(" {0}) ", parameters);
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDemand t = new recDemand(true);
                                t.row_number = dr[0] == DBNull.Value ? 0 : int.Parse(dr[0].ToString());
                                t.DemandNo = dr[recDemand.Fields.DemandNo] == DBNull.Value ? 0 : int.Parse(dr[recDemand.Fields.DemandNo].ToString());
                                t.DebtorName = dr[recDemand.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDemand.Fields.DebtorName].ToString();
                                t.recDemandDetail.TransName = dr[recDemandDetail.Fields.TransName] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.TransName].ToString();
                                t.recDemandDetail.TransValue = dr[recDemandDetail.Fields.TransValue] == DBNull.Value ? (decimal?)0 : decimal.Parse(dr[recDemandDetail.Fields.TransValue].ToString());
                                t.recDemandDetail.DueDate = dr[recDemandDetail.Fields.DueDate] == DBNull.Value ? (DateTime)(DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.DueDate].ToString());
                                t.recDemandDetail.DueDateLetter = dr[recDemandDetail.Fields.DueDateLetter] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.DueDateLetter].ToString());
                                t.recDemandDetail.LatterDate = dr[recDemandDetail.Fields.LatterDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.LatterDate].ToString());
                                t.recDemandDetail.LatterNo = dr[recDemandDetail.Fields.LatterNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.LatterNo].ToString();
                                t.LastAction = dr[recDemand.Fields.LastAction] == DBNull.Value ? string.Empty : dr[recDemand.Fields.LastAction].ToString();
                                
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public recDemand GetAttachFileByDemandNo(int demandNo)
        {
            recDemand t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select AttachFileName,AttachFile from transactions.recDemandDetail WHERE DemandNo = @DemandNo";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DemandNo", SqlDbType.Int).Value = demandNo;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recDemand(true);
                                t.recDemandDetail.AttachFileName = dr[recDemandDetail.Fields.AttachFileName] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.AttachFileName].ToString();
                                t.recDemandDetail.AttachFile = dr[recDemandDetail.Fields.AttachFile] == DBNull.Value ? null : (byte[])(dr[recDemandDetail.Fields.AttachFile]);
                                
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public bool UpdateUpayaPenagihan(recDemand demand)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = @"
                                DECLARE	@return_value int,
		                            @hasil varchar(100)
                                        EXEC	@return_value = [dbo].[SP_GenTransCode]
		                                        @kode = N'13',
		                                        @hasil = @hasil OUTPUT
                                    SELECT	@hasil as N'@hasil'";

                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = txn;
                    var temp = cmd.ExecuteScalar();
                    string transNo = temp.ToString();
                    cmd.Dispose();

//                    cmd = conn.CreateCommand();
//                    cmd.CommandText = @"
//                                select ActionName
//                                from master.recActionDemand
//                        where ActionID = " + demand.recDemandDetail.StatusID;

//                    cmd.CommandType = CommandType.Text;
//                    cmd.Transaction = txn;
//                    var temp1 = cmd.ExecuteScalar();
//                    string statusName = temp1.ToString();
//                    cmd.Dispose();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "update transactions.recDemandDetail set TransNo = @transNo,LatterDate = @LatterDate, LatterNo = @LatterNo,DueDateLetter = @dueDateLetter, TransTypeID = @transTypeId, TransName = @transName, StatusID = @statusId, [Status] = @status, UpdatedDate = @updatedDate, UpdatedBy = @updatedBy, AttachFile = @AttachFile, AttachFileName = @AttachFileName where DemandNo = @demandNo";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DemandNo", SqlDbType.Int).Value = demand.DemandNo;
                    cmd.Parameters.Add("@TransNo", SqlDbType.VarChar).Value = transNo;
                    cmd.Parameters.Add("@TransTypeID", SqlDbType.Int).Value = demand.recDemandDetail.TransTypeID;
                    cmd.Parameters.Add("@TransName", SqlDbType.VarChar).Value = demand.recDemandDetail.TransName;
                    cmd.Parameters.Add("@StatusID", SqlDbType.Int).Value = (object)demand.recDemandDetail.StatusID ?? DBNull.Value;
                    cmd.Parameters.Add("@Status", SqlDbType.VarChar).Value = (object)demand.recDemandDetail.Status ?? DBNull.Value;
                    cmd.Parameters.Add("@DueDateLetter", SqlDbType.Date).Value = (object)demand.recDemandDetail.DueDateLetter ?? DBNull.Value;
                    cmd.Parameters.Add("@LatterDate", SqlDbType.Date).Value = (object)demand.recDemandDetail.LatterDate ?? DBNull.Value;
                    cmd.Parameters.Add("@LatterNo", SqlDbType.VarChar).Value = (object)demand.recDemandDetail.LatterNo ?? DBNull.Value;
                    cmd.Parameters.Add("@AttachFile", SqlDbType.VarBinary).Value = (object)demand.recDemandDetail.AttachFile ?? DBNull.Value;
                    cmd.Parameters.Add("@AttachFileName", SqlDbType.VarChar).Value = (object)demand.recDemandDetail.AttachFileName ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)demand.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)demand.UpdatedBy ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // update recDemandDetail
                    cmd.Dispose();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "update transactions.recDemand set LastAction = @LastAction where DemandNo = @DemandNo1";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DemandNo1", SqlDbType.Int).Value = demand.DemandNo;
                    cmd.Parameters.Add("@LastAction", SqlDbType.VarChar).Value = (object)demand.LastAction ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // update recDemand.lastAction
                    cmd.Dispose();

                    if (demand.recDemandDetail.StatusID == 4)
                    {
                        cmd = conn.CreateCommand();
                        cmd.CommandText = "update transactions.recDemand set recDemand.IsActive = 0 where recDemand.DemandNo = @DemandNo";
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add("@DemandNo", SqlDbType.VarChar).Value = demand.DemandNo;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery(); // update recDemand.IsActive
                        cmd.Dispose();
                    }

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

        public bool insertUpayaPenagihan(recDemandDetail demandDetail)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    // generate prosedur genTransCode
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = @"
                                DECLARE	@return_value int,
		                            @hasil varchar(100)
                                        EXEC	@return_value = [dbo].[SP_GenTransCode]
		                                        @kode = N'13',
		                                        @hasil = @hasil OUTPUT
                                    SELECT	@hasil as N'@hasil'";

                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = txn;
                    var temp = cmd.ExecuteScalar();
                    string transNo = temp.ToString();
                    cmd.Dispose();

                    // get max dari recDemandDetailNo
                    cmd = conn.CreateCommand();
                    cmd.CommandText = @"
                                begin
                                    declare @max int = 1;

                                    if Exists(SELECT MAX(DemandDetailNo) DemandDetailNo FROM transactions.recDemandDetail )
	                                    set @max = (SELECT MAX(DemandDetailNo)+1 DemandDetailNo FROM transactions.recDemandDetail);
                                    else
	                                    set @max = 1;

                                    select @max;	
                                end";

                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = txn;
                    var temp1 = cmd.ExecuteScalar();
                    int demandDetailNo = int.Parse(temp1.ToString());
                    cmd.Dispose();

                    //cari status name
//                    cmd = conn.CreateCommand();
//                    cmd.CommandText = @"
//                                select ActionName
//                                from master.recActionDemand
//                        where ActionID = " + demandDetail.StatusID;

//                    cmd.CommandType = CommandType.Text;
//                    cmd.Transaction = txn;
//                    var temp2 = cmd.ExecuteScalar();
//                    string statusName = temp2.ToString();
//                    cmd.Dispose();

                    //mencari data dueDate yang belum
                    cmd = conn.CreateCommand();
                    cmd.CommandText = @"
                                select
                                    DueDate
                                from 
                                    transactions.recDemandDetail
                                where DemandNo = " + demandDetail.DemandNo;

                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = txn;
                    var temp3 = cmd.ExecuteScalar();
                    DateTime dueDate = DateTime.Parse(temp3.ToString());
                    cmd.Dispose();

                    //mencari data transValue yang belum
                    cmd = conn.CreateCommand();
                    cmd.CommandText = @"
                                select
                                    TransValue
                                from 
                                    transactions.recDemandDetail
                                where DemandNo = " + demandDetail.DemandNo;

                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = txn;
                    var temp4 = cmd.ExecuteScalar();
                    decimal transValue = decimal.Parse(temp4.ToString());
                    cmd.Dispose();

                    //mencari data billingNo yang belum
                    cmd = conn.CreateCommand();
                    cmd.CommandText = @"
                                select
                                    BillingNo
                                from 
                                    transactions.recDemandDetail
                                where DemandNo = " + demandDetail.DemandNo;

                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = txn;
                    var temp5 = cmd.ExecuteScalar();
                    string billingNo = temp5.ToString();
                    cmd.Dispose();

                    //mencari data InvoiceNo yang belum
                    cmd = conn.CreateCommand();
                    cmd.CommandText = @"
                                select
                                    InvoiceNo
                                from 
                                    transactions.recDemandDetail
                                where DemandNo = " + demandDetail.DemandNo;

                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = txn;
                    var temp6 = cmd.ExecuteScalar();
                    string invoiceNo = temp6.ToString();
                    cmd.Dispose();

                    //insert recDemandDetail
                    cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO transactions.recDemandDetail(DemandDetailNo, DemandNo, TransNo, TransTypeID, TransName, DueDate, TransValue, StatusID, Status, BillingNo, InvoiceNo, LatterNo, DueDateLetter, LatterDate, SanctionNo, AttachFileName, AttachFile, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@DemandDetailNo, @DemandNo, @TransNo, @TransTypeID, @TransName, @DueDate, @TransValue, @StatusID, @Status, @BillingNo, @InvoiceNo, @LatterNo, @DueDateLetter, @LatterDate, @SanctionNo, @AttachFileName, @AttachFile, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DemandDetailNo", SqlDbType.Int).Value = demandDetailNo;
                    cmd.Parameters.Add("@DemandNo", SqlDbType.Int).Value = demandDetail.DemandNo;
                    cmd.Parameters.Add("@TransNo", SqlDbType.VarChar).Value = transNo;
                    cmd.Parameters.Add("@TransTypeID", SqlDbType.Int).Value = demandDetail.TransTypeID;
                    cmd.Parameters.Add("@TransName", SqlDbType.VarChar).Value = demandDetail.TransName;
                    cmd.Parameters.Add("@DueDate", SqlDbType.Date).Value = dueDate;
                    cmd.Parameters.Add("@TransValue", SqlDbType.Decimal).Value = transValue;
                    cmd.Parameters.Add("@StatusID", SqlDbType.Int).Value = (object)demandDetail.StatusID ?? DBNull.Value;
                    cmd.Parameters.Add("@Status", SqlDbType.VarChar).Value = (object)demandDetail.Status ?? DBNull.Value; ;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = billingNo;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceNo;
                    cmd.Parameters.Add("@LatterNo", SqlDbType.VarChar).Value = (object)demandDetail.LatterNo ?? DBNull.Value;
                    cmd.Parameters.Add("@DueDateLetter", SqlDbType.Date).Value = (object)demandDetail.DueDateLetter ?? DBNull.Value;
                    cmd.Parameters.Add("@LatterDate", SqlDbType.Date).Value = (object)demandDetail.LatterDate ?? DBNull.Value;
                    cmd.Parameters.Add("@SanctionNo", SqlDbType.VarChar).Value = (object)demandDetail.SanctionNo ?? DBNull.Value;
                    cmd.Parameters.Add("@AttachFileName", SqlDbType.VarChar).Value = (object)demandDetail.AttachFileName ?? DBNull.Value;
                    cmd.Parameters.Add("@AttachFile", SqlDbType.VarBinary).Value = (object)demandDetail.AttachFile ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)demandDetail.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)demandDetail.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)demandDetail.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)demandDetail.UpdatedBy ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // insert recDemandDetail
                    cmd.Dispose();

                    //update recDemand lastAction
                    cmd = conn.CreateCommand();
                    cmd.CommandText = "update transactions.recDemand set LastAction = @LastAction where DemandNo = @DemandNo1";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DemandNo1", SqlDbType.Int).Value = demandDetail.DemandNo;
                    cmd.Parameters.Add("@LastAction", SqlDbType.VarChar).Value = demandDetail.Status;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // update recDemand lastAction
                    cmd.Dispose();

                    //update recDemand isActive = 0 jika statusID = 3
                    if (demandDetail.StatusID == 4)
                    {
                        cmd = conn.CreateCommand();
                        cmd.CommandText = "update transactions.recDemand set recDemand.IsActive = 0 where recDemand.DemandNo = @DemandNo";
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add("@DemandNo", SqlDbType.VarChar).Value = demandDetail.DemandNo;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery(); // update recDemand.IsActive
                        cmd.Dispose();
                    }

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

        /* Wilayah 1C */
        public object SelectScalarDemandDetailByDebtorID(SQL.Function.Aggregate function, string column, string parameters, string debtID) {

            object _result = null;
            string append = string.Empty;

            if (parameters == null || parameters.Equals("") || parameters == string.Empty)
            {
                append = "";
            }
            else {
                append = append.Replace("WHERE", "AND");
            }
            
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM [transactions].[recDemandDetail]", column);
                            sbQuery.AppendFormat(@" WHERE [transactions].[recDemandDetail].[DemandNo] in  
                                                    (SELECT  [DemandNo]
                                                        FROM [transactions].[recDemand]
                                                        WHERE debtorID={0} AND isActive=0) {1}", debtID, append);

                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM [transactions].[recDemandDetail]", column);
                            sbQuery.AppendFormat(@" WHERE [transactions].[recDemandDetail].[DemandNo] in  
                                                    (SELECT  [DemandNo]
                                                        FROM [transactions].[recDemand]
                                                        WHERE debtorID={0} AND isActive=0) {1}", debtID, append);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM [transactions].[recDemandDetail]", column);
                            sbQuery.AppendFormat(@" WHERE [transactions].[recDemandDetail].[DemandNo] in  
                                                    (SELECT  [DemandNo]
                                                        FROM [transactions].[recDemand]
                                                        WHERE debtorID={0} AND isActive=0) {1}", debtID, append);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM [transactions].[recDemandDetail]", column);
                            sbQuery.AppendFormat(@" WHERE [transactions].[recDemandDetail].[DemandNo] in  
                                                    (SELECT  [DemandNo]
                                                        FROM [transactions].[recDemand]
                                                        WHERE debtorID={0} AND isActive=1) {1}", debtID, append);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM [transactions].[recDemandDetail]", column);
                            sbQuery.AppendFormat(@" WHERE [transactions].[recDemandDetail].[DemandNo] in  
                                                    (SELECT  [DemandNo]
                                                        FROM [transactions].[recDemand]
                                                        WHERE debtorID={0} AND isActive=0) {1}", debtID, append);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM [transactions].[recDemandDetail]", column);
                            sbQuery.AppendFormat(@" WHERE [transactions].[recDemandDetail].[DemandNo] in  
                                                    (SELECT  [DemandNo]
                                                        FROM [transactions].[recDemand]
                                                        WHERE debtorID={0} AND isActive=0) {1}", debtID, append);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }
        public List<recDemandDetail> GetListDemandDetailByDebtorID(int pageSize, int currentPage, string sortName, string sortOrder, string parameters, string debtID)
        {
            List<recDemandDetail> recDemandDetails = new List<recDemandDetail>();
            string append = string.Empty;

            if (parameters == null || parameters.Equals("") || parameters == string.Empty)
            {
                append = "";
            }
            else
            {
                append = append.Replace("WHERE", "AND");
            }

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"      [DemandDetailNo]
                                                  ,[DemandNo]
                                                  ,[TransNo]
                                                  ,[TransTypeID]
                                                  ,[TransName]
                                                  ,[DueDate]
                                                  ,[TransValue]
                                                  ,[StatusID]
                                                  ,[Status]
                                                  ,[BillingNo]
                                                  ,[InvoiceNo]
                                                  ,[LatterNo]
                                                  ,[DueDateLetter]
                                                  ,[LatterDate]
                                                  ,[SanctionNo]
                                                  ,[AttachFile]
                                                  ,[CreatedDate]
                                                  ,[CreatedBy]
                                                  ,[UpdatedDate]
                                                  ,[UpdatedBy]");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"      [transactions].[recDemandDetail]
                                              where [transactions].[recDemandDetail].[DemandNo] in  
                                                  (SELECT  [DemandNo]
                                                    FROM [transactions].[recDemand]
                                                    WHERE debtorID={0} AND isActive=0) {1}", debtID, append);
                    sbQuery.AppendFormat(") ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDemandDetail t = new recDemandDetail();
                                t.DemandDetailNo = dr[recDemandDetail.Fields.DemandDetailNo] == DBNull.Value ? 0 : int.Parse(dr[recDemandDetail.Fields.DemandDetailNo].ToString());
                                t.DemandNo = dr[recDemandDetail.Fields.DemandNo] == DBNull.Value ? 0 : int.Parse(dr[recDemandDetail.Fields.DemandNo].ToString());
                                t.TransNo = dr[recDemandDetail.Fields.TransNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.TransNo].ToString();
                                t.TransTypeID = dr[recDemandDetail.Fields.TransTypeID] == DBNull.Value ? 0 : int.Parse(dr[recDemandDetail.Fields.TransTypeID].ToString());
                                t.TransName = dr[recDemandDetail.Fields.TransName] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.TransName].ToString();
                                t.DueDate = dr[recDemandDetail.Fields.DueDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recDemandDetail.Fields.DueDate].ToString());
                                t.TransValue = dr[recDemandDetail.Fields.TransValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recDemandDetail.Fields.TransValue].ToString());
                                t.StatusID = dr[recDemandDetail.Fields.StatusID] == DBNull.Value ? (int?)null : int.Parse(dr[recDemandDetail.Fields.StatusID].ToString());
                                t.Status = dr[recDemandDetail.Fields.Status] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.Status].ToString();
                                t.BillingNo = dr[recDemandDetail.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.BillingNo].ToString();
                                t.InvoiceNo = dr[recDemandDetail.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.InvoiceNo].ToString();
                                t.LatterNo = dr[recDemandDetail.Fields.LatterNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.LatterNo].ToString();
                                t.DueDateLetter = dr[recDemandDetail.Fields.DueDateLetter] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.DueDateLetter].ToString());
                                t.LatterDate = dr[recDemandDetail.Fields.LatterDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.LatterDate].ToString());
                                t.SanctionNo = dr[recDemandDetail.Fields.SanctionNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.SanctionNo].ToString();
                                t.AttachFile = dr[recDemandDetail.Fields.AttachFile] == DBNull.Value ? null : (byte[])(dr[recDemandDetail.Fields.AttachFile]);
                                t.CreatedDate = dr[recDemandDetail.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDemandDetail.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDemandDetail.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDemandDetail.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.UpdatedBy].ToString();
                                recDemandDetails.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return recDemandDetails;
        }

        public List<recDemandDetail> GetListDemandDetailByDebtorID(int debtID)
        {
            List<recDemandDetail> recDemandDetails = new List<recDemandDetail>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"  select [DemandDetailNo]
                                                  ,[DemandNo]
                                                  ,[TransNo]
                                                  ,[TransTypeID]
                                                  ,[TransName]
                                                  ,[DueDate]
                                                  ,[TransValue]
                                                  ,[StatusID]
                                                  ,[Status]
                                                  ,[BillingNo]
                                                  ,[InvoiceNo]
                                                  ,[LatterNo]
                                                  ,[DueDateLetter]
                                                  ,[LatterDate]
                                                  ,[SanctionNo]
                                                  ,[AttachFile]
                                                  ,[CreatedDate]
                                                  ,[CreatedBy]
                                                  ,[UpdatedDate]
                                                  ,[UpdatedBy] from [transactions].[recDemandDetail] 
                            where [transactions].[recDemandDetail].[DemandNo] in  
                            (SELECT  [DemandNo]
                            FROM [transactions].[recDemand]
                            where debtorID = " + debtID + ")";
                    
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDemandDetail t = new recDemandDetail();
                                t.DemandDetailNo = dr[recDemandDetail.Fields.DemandDetailNo] == DBNull.Value ? 0 : int.Parse(dr[recDemandDetail.Fields.DemandDetailNo].ToString());
                                t.DemandNo = dr[recDemandDetail.Fields.DemandNo] == DBNull.Value ? 0 : int.Parse(dr[recDemandDetail.Fields.DemandNo].ToString());
                                t.TransNo = dr[recDemandDetail.Fields.TransNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.TransNo].ToString();
                                t.TransTypeID = dr[recDemandDetail.Fields.TransTypeID] == DBNull.Value ? 0 : int.Parse(dr[recDemandDetail.Fields.TransTypeID].ToString());
                                t.TransName = dr[recDemandDetail.Fields.TransName] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.TransName].ToString();
                                t.DueDate = dr[recDemandDetail.Fields.DueDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recDemandDetail.Fields.DueDate].ToString());
                                t.TransValue = dr[recDemandDetail.Fields.TransValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recDemandDetail.Fields.TransValue].ToString());
                                t.StatusID = dr[recDemandDetail.Fields.StatusID] == DBNull.Value ? (int?)null : int.Parse(dr[recDemandDetail.Fields.StatusID].ToString());
                                t.Status = dr[recDemandDetail.Fields.Status] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.Status].ToString();
                                t.BillingNo = dr[recDemandDetail.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.BillingNo].ToString();
                                t.InvoiceNo = dr[recDemandDetail.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.InvoiceNo].ToString();
                                t.LatterNo = dr[recDemandDetail.Fields.LatterNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.LatterNo].ToString();
                                t.DueDateLetter = dr[recDemandDetail.Fields.DueDateLetter] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.DueDateLetter].ToString());
                                t.LatterDate = dr[recDemandDetail.Fields.LatterDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.LatterDate].ToString());
                                t.SanctionNo = dr[recDemandDetail.Fields.SanctionNo] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.SanctionNo].ToString();
                                t.AttachFile = dr[recDemandDetail.Fields.AttachFile] == DBNull.Value ? null : (byte[])(dr[recDemandDetail.Fields.AttachFile]);
                                t.CreatedDate = dr[recDemandDetail.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDemandDetail.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDemandDetail.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDemandDetail.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDemandDetail.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDemandDetail.Fields.UpdatedBy].ToString();
                                recDemandDetails.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return recDemandDetails;
        }
    }
}
